﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Koonsoft.HYCRM.Model;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Koonsoft.HYCRM.DAL
{
    public class AuditorsDAL
    {
        private static readonly Database Database = DatabaseFactory.CreateDatabase("KOONCRMDB");
        /// <summary>
        /// 新增银行对账单
        /// </summary>
        public void CreateCompanyBank(CreateBankEntity entity, DbTransaction transaction)
        {
            const string sql = @"CreateCompanyBank";


            using (var cmd = Database.GetStoredProcCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, entity.CompanyID);
                Database.AddInParameter(cmd, "@Date", DbType.String, entity.Date);
                Database.AddInParameter(cmd, "@Explain", DbType.String, entity.Explain);
                Database.AddInParameter(cmd, "@Amount", DbType.String, entity.Amount);
                Database.AddInParameter(cmd, "@Currency", DbType.String, entity.Currency);
                Database.AddInParameter(cmd, "@VoucherNo", DbType.String, entity.VoucherNo);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, entity.OrderID);

                Database.ExecuteNonQuery(cmd, transaction);
            }
        }

        /// <summary>
        /// 新增银行对账单
        /// </summary>
        public void CreateCompanyBank(CreateBankEntity entity)
        {
            const string sql = @"CreateCompanyBank";


            using (var cmd = Database.GetStoredProcCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, entity.CompanyID);
                Database.AddInParameter(cmd, "@Date", DbType.String, entity.Date);
                Database.AddInParameter(cmd, "@Explain", DbType.String, entity.Explain);
                Database.AddInParameter(cmd, "@Amount", DbType.String, entity.Amount);
                Database.AddInParameter(cmd, "@Currency", DbType.String, entity.Currency);
                Database.AddInParameter(cmd, "@VoucherNo", DbType.String, entity.VoucherNo);

                Database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 新增银行对账单
        /// </summary>
        public bool CreateCompanyBank(List<CreateBankEntity> list, out string msg)
        {
            msg = "";

            using (DbConnection conn = Database.CreateConnection())
            {
                conn.Open();
                DbTransaction tran = conn.BeginTransaction();

                try
                {
                    foreach (var v in list)
                    {
                        CreateCompanyBank(v, tran);
                    }
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    msg = ex.Message;
                    tran.Rollback();
                    return false;
                }

            }
            return true;
        }

        /// <summary>
        /// 新增银行对账单
        /// </summary>
        public bool CreateCompanyBank(CreateBankEntity entity, out string msg)
        {
            msg = "";

            try
            {
                CreateCompanyBank(entity);
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                return false;
            }
            return true;
        }



        /// <summary>
        /// 获取银行对账单
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <returns></returns>
        public DataTable GetCompanyBank(string CompanyID, string OrderID)
        {
            const string procedure = "select * from CompanyBank where CompanyId =@CompanyId and @OrderID =OrderID";

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {
                Database.AddInParameter(cmd, "@CompanyId", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, OrderID);

                DataSet ds = Database.ExecuteDataSet(cmd);

                return ds.Tables[0];
            }
        }

        /// <summary>
        /// 获取银行对账单
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <returns></returns>
        public DataTable GetCompanyBank(string CompanyID, string OrderID, string Currency)
        {
            const string procedure = "select * from CompanyBank where CompanyId =@CompanyId and @OrderID =OrderID and currency =@Currency   order  by [date] ";

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {
                Database.AddInParameter(cmd, "@CompanyId", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, OrderID);
                Database.AddInParameter(cmd, "@Currency", DbType.String, Currency);

                DataSet ds = Database.ExecuteDataSet(cmd);

                return ds.Tables[0];
            }
        }

        ///// <summary>
        ///// 获取银行对账单 支票账户
        ///// currency =99 
        ///// </summary>
        ///// <param name="CompanyID"></param>
        ///// <returns></returns>
        //public DataTable GetCompanyBankCheck(string CompanyID, string OrderID)
        //{
        //    const string procedure = "select * from CompanyBank where CompanyId =@CompanyId and @OrderID =OrderID and currency =99 ";

        //    using (var cmd = Database.GetSqlStringCommand(procedure))
        //    {
        //        Database.AddInParameter(cmd, "@CompanyId", DbType.String, CompanyID);
        //        Database.AddInParameter(cmd, "@OrderID", DbType.String, OrderID);

        //        DataSet ds = Database.ExecuteDataSet(cmd);

        //        return ds.Tables[0];
        //    }
        //}

        ///// <summary>
        ///// 获取银行对账单 储蓄账户
        ///// currency =98
        ///// </summary>
        ///// <param name="CompanyID"></param>
        ///// <returns></returns>
        //public DataTable GetCompanyBankSavings(string CompanyID, string OrderID)
        //{
        //    const string procedure = "select * from CompanyBank where CompanyId =@CompanyId and @OrderID =OrderID and currency =98 ";

        //    using (var cmd = Database.GetSqlStringCommand(procedure))
        //    {
        //        Database.AddInParameter(cmd, "@CompanyId", DbType.String, CompanyID);
        //        Database.AddInParameter(cmd, "@OrderID", DbType.String, OrderID);

        //        DataSet ds = Database.ExecuteDataSet(cmd);

        //        return ds.Tables[0];
        //    }
        //}

        ///// <summary>
        ///// 获取银行对账单 美金账户
        ///// currency =2
        ///// </summary>
        ///// <param name="CompanyID"></param>
        ///// <returns></returns>
        //public DataTable GetCompanyBankUSA(string CompanyID, string OrderID)
        //{
        //    const string procedure = "select * from CompanyBank where CompanyId =@CompanyId and @OrderID =OrderID and currency = 2 ";

        //    using (var cmd = Database.GetSqlStringCommand(procedure))
        //    {
        //        Database.AddInParameter(cmd, "@CompanyId", DbType.String, CompanyID);
        //        Database.AddInParameter(cmd, "@OrderID", DbType.String, OrderID);

        //        DataSet ds = Database.ExecuteDataSet(cmd);

        //        return ds.Tables[0];
        //    }
        //}

        /// <summary>
        /// 获取银行对账单 其他
        /// currency not in (1,98,99) 
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <returns></returns>
        public DataTable GetCompanyBankOther(string CompanyID, string OrderID)
        {
            const string procedure = "select * from CompanyBank where CompanyId =@CompanyId and @OrderID =OrderID and currency not in   (1,2,98,99)  order  by currency,[date] ";

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {
                Database.AddInParameter(cmd, "@CompanyId", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, OrderID);

                DataSet ds = Database.ExecuteDataSet(cmd);

                return ds.Tables[0];
            }
        }

        /// <summary>
        /// 获取银行结余
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <returns></returns>
        public double GetCashAtBank(string CompanyID, string orderid)
        {
            const string sql = "GetallCashAtBank";

            using (var cmd = Database.GetStoredProcCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@orderID", DbType.String, orderid);

                return Convert.ToDouble(Database.ExecuteScalar(cmd));
            }
        }

        /// <summary>
        /// 获取购货凭证
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <returns></returns>
        public DataTable GetCompanyPurchase(string CompanyID, string OrderID)
        {
            const string procedure = @"select a.*,exchangeRate as ex from CompanyPurchase a
                                        join exchangeRate b on a.currency =b.[source] and [target] = 1 and CONVERT(varchar(6) , a.[date], 112 ) =CONVERT(varchar(6) ,b.[date], 112 )
                                            where CompanyId =@CompanyId and @OrderID = OrderID";

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {
                Database.AddInParameter(cmd, "@CompanyId", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, OrderID);

                DataSet ds = Database.ExecuteDataSet(cmd);

                return ds.Tables[0];
            }
        }

        /// <summary>
        /// 获取销货凭证
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <returns></returns>
        public DataTable GetCompanySales(string CompanyID, string orderID)
        {
            const string procedure = @"select a.*,exchangeRate as ex from CompanySales a
                                        join exchangeRate b on a.currency =b.[source] and [target] = 1 and CONVERT(varchar(6) , a.[date], 112 ) =CONVERT(varchar(6) ,b.[date], 112 )
                                             where CompanyId =@CompanyId and @orderID = orderID";

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {
                Database.AddInParameter(cmd, "@CompanyId", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@orderID", DbType.String, orderID);

                DataSet ds = Database.ExecuteDataSet(cmd);

                return ds.Tables[0];
            }
        }

        /// <summary>
        /// 获取汇率
        /// </summary>
        /// <returns></returns>
        public List<ExchangeRateEntity> GetExchangeRate()
        {
            List<ExchangeRateEntity> list = new List<ExchangeRateEntity>();
            const string procedure = "select * from ExchangeRate";

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {


                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);
                var sqlrd = (SqlDataReader)rd.InnerReader;

                while (sqlrd.Read())
                {
                    var v = new ExchangeRateEntity()
                    {
                        Date = DateTime.Parse(sqlrd["Date"].ToString()).ToString("yyyyMM"),
                        ExchangeRate = Convert.ToDouble(sqlrd["ExchangeRate"].ToString()),
                        Source = sqlrd["Source"].ToString(),
                        Traget = sqlrd["Target"].ToString()
                    };
                    list.Add(v);

                }

            }

            return list;
        }

        /// <summary>
        /// 获取汇率
        /// </summary>
        /// <returns></returns>
        public ExchangeRateEntity GetExchangeRate(string orderid, string source)
        {
            string procedure;
            if (source == "3")
            {
                procedure =
                    @"select [date],Source,Target,round(1 / 0.821987,4) as ExchangeRate  from dbo.CompanyAuditors a
                        join exchangeRate b on CONVERT(varchar(6) , b.[date], 112 ) =CONVERT(varchar(6) ,a.endtime, 112 )  and source=@source   
                        where orderid = @orderid";
            }
            else
            {
                procedure =
                   @"select [date],Source,Target,ExchangeRate  from dbo.CompanyAuditors a
                        join exchangeRate b on CONVERT(varchar(6) , b.[date], 112 ) =CONVERT(varchar(6) ,a.endtime, 112 )  and source=@source   
                        where orderid = @orderid";
            }

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {

                Database.AddInParameter(cmd, "@orderid", DbType.String, orderid);
                Database.AddInParameter(cmd, "@source", DbType.String, source);

                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);
                var sqlrd = (SqlDataReader)rd.InnerReader;

                if(sqlrd.Read())
                {
                    var v = new ExchangeRateEntity()
                    {
                        Date = DateTime.Parse(sqlrd["Date"].ToString()).ToString("yyyyMM"),
                        ExchangeRate = Convert.ToDouble(sqlrd["ExchangeRate"].ToString()),
                        Source = sqlrd["Source"].ToString(),
                        Traget = sqlrd["Target"].ToString()
                    };

                    return v;
                }

            }

            return null;
        }

        /// <summary>
        /// 获取币种
        /// </summary>
        /// <returns></returns>
        public List<CurrencyEntity> GetExchangeCurrency()
        {
            List<CurrencyEntity> list = new List<CurrencyEntity>();
            const string procedure = "select * from Currency";

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {
                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);
                var sqlrd = (SqlDataReader)rd.InnerReader;

                while (sqlrd.Read())
                {
                    var v = new CurrencyEntity()
                    {
                        CurrencyId = sqlrd["CurrencyId"].ToString(),
                        CurrencyName = sqlrd["CurrencyName"].ToString(),
                        CurrencyValue = sqlrd["CurrencyValue"].ToString()
                    };
                    list.Add(v);
                }
            }
            return list;
        }


        /// <summary>
        /// 新增销售凭证
        /// </summary>
        public bool CreateCompanySales(List<CreateSalesEntity> list, out string msg)
        {
            msg = "";

            using (DbConnection conn = Database.CreateConnection())
            {
                conn.Open();
                DbTransaction tran = conn.BeginTransaction();

                try
                {
                    foreach (var v in list)
                    {
                        CreateSales(v, tran);
                    }
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    msg = ex.Message;
                    tran.Rollback();
                    return false;
                }

            }
            return true;
        }



        /// <summary>
        /// 新增销售明细
        /// </summary>
        public void CreateSales(CreateSalesEntity entity, DbTransaction transaction)
        {
            //            const string sql = @"
            //INSERT INTO [CompanySales]
            //           ([CompanyId]
            //           ,[Date]
            //           ,[VoucherNo]
            //           ,[OrderNo]
            //           ,[Customer]
            //           ,[Offering]
            //           ,[Currency]
            //           ,[Amount]
            //           ,[HKMoney]
            //            ,OrderID)
            //     VALUES(@CompanyId,@Date,@VoucherNo,@OrderNo,@Customer,@Offering,@Currency,@Amount,@HKMoney,@OrderID)";


            //            using (var cmd = Database.GetSqlStringCommand(sql))
            const string sql = @"CreateCompanySales";


            using (var cmd = Database.GetStoredProcCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, entity.CompanyID);
                Database.AddInParameter(cmd, "@Date", DbType.String, entity.Date);
                Database.AddInParameter(cmd, "@VoucherNo", DbType.String, entity.VoucherNo);
                Database.AddInParameter(cmd, "@OrderNo", DbType.String, entity.OrderNo);
                Database.AddInParameter(cmd, "@Customer", DbType.String, entity.Customer);
                Database.AddInParameter(cmd, "@Offering", DbType.String, entity.Offering);
                Database.AddInParameter(cmd, "@Currency", DbType.String, entity.Currency);
                Database.AddInParameter(cmd, "@Amount", DbType.String, entity.Amount);
                //Database.AddInParameter(cmd, "@HKMoney", DbType.String, entity.HKMoney);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, entity.OrderID);

                Database.ExecuteNonQuery(cmd, transaction);
            }
        }

        /// <summary>
        /// 新增购货明细
        /// </summary>
        public bool CreateCompanyPurchase(List<CreatePurchaseEntity> list, out string msg)
        {
            msg = "";

            using (DbConnection conn = Database.CreateConnection())
            {
                conn.Open();
                DbTransaction tran = conn.BeginTransaction();

                try
                {
                    foreach (var v in list)
                    {
                        CreatePurchase(v, tran);
                    }
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    msg = ex.Message;
                    tran.Rollback();
                    return false;
                }

            }
            return true;
        }

        /// <summary>
        /// 新增购货明细
        /// </summary>
        public void CreatePurchase(CreatePurchaseEntity entity, DbTransaction transaction)
        {
            //            const string sql = @"
            //INSERT INTO [CompanyPurchase]
            //           ([CompanyId]
            //              ,[Date]
            //              ,[VoucherNo]
            //              ,[PONo]
            //              ,[Supplier]
            //              ,[Offering]
            //              ,[Currency]
            //              ,[Amount]
            //              ,[HKMoney],OrderID)
            //     VALUES(@CompanyId,@Date,@VoucherNo,@PONo,@Supplier,@Offering,@Currency,@Amount,@HKMoney,@OrderID)";


            //            using (var cmd = Database.GetSqlStringCommand(sql))
            const string sql = @"CreateCompanyPurchase";


            using (var cmd = Database.GetStoredProcCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, entity.CompanyID);
                Database.AddInParameter(cmd, "@Date", DbType.String, entity.Date);
                Database.AddInParameter(cmd, "@VoucherNo", DbType.String, entity.VoucherNo);
                Database.AddInParameter(cmd, "@PONo", DbType.String, entity.PoNo);
                Database.AddInParameter(cmd, "@Supplier", DbType.String, entity.Supplier);
                Database.AddInParameter(cmd, "@Offering", DbType.String, entity.Offering);
                Database.AddInParameter(cmd, "@Currency", DbType.String, entity.Currency);
                Database.AddInParameter(cmd, "@Amount", DbType.String, entity.Amount);
                //Database.AddInParameter(cmd, "@HKMoney", DbType.String, entity.HKMoney);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, entity.OrderId);

                Database.ExecuteNonQuery(cmd, transaction);
            }
        }

        /// <summary>
        /// 获取类型提供者
        /// </summary>
        /// <returns></returns>
        public List<TypeProviderEntity> GetTypeProvider()
        {
            List<TypeProviderEntity> list = new List<TypeProviderEntity>();
            const string procedure = "select * from DetailTypeProvider";

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {
                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);
                var sqlrd = (SqlDataReader)rd.InnerReader;

                while (sqlrd.Read())
                {
                    var v = new TypeProviderEntity()
                    {
                        TypeProviderID = sqlrd["TypeProviderID"].ToString(),
                        TypeProviderName = sqlrd["TypeProviderName"].ToString()
                    };
                    list.Add(v);
                }
            }
            return list;
        }

        /// <summary>
        /// 新增购货明细类型
        /// </summary>
        public void CreateDetailTypes(string name, string EnglishNamem, string typeProviderID, string kinds)
        {
            const string sql = @"
                    INSERT INTO [DetailTypes]
                               ([TypeName],[TypeProviderID],[kinds],EnglishName)
                         VALUES(@TypeName,@TypeProviderID,@kinds,@EnglishNamem)";

            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@TypeName", DbType.String, name);
                Database.AddInParameter(cmd, "@TypeProviderID", DbType.String, typeProviderID);
                Database.AddInParameter(cmd, "@kinds", DbType.String, kinds);
                Database.AddInParameter(cmd, "@EnglishNamem", DbType.String, EnglishNamem);

                Database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 获取类型
        /// </summary>
        /// <returns></returns>
        public List<DetailTypesEntity> GetDetailTypes()
        {
            List<DetailTypesEntity> list = new List<DetailTypesEntity>();
            const string procedure = "select * from DetailTypes";

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {
                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);
                var sqlrd = (SqlDataReader)rd.InnerReader;

                while (sqlrd.Read())
                {
                    var v = new DetailTypesEntity()
                    {
                        TypeId = sqlrd["TypeId"].ToString(),
                        TypeName = sqlrd["TypeName"].ToString(),
                        TypeProviderID = sqlrd["TypeProviderID"].ToString(),
                        Kinds = sqlrd["Kinds"].ToString(),
                        EnglishName = sqlrd["EnglishName"].ToString()
                    };
                    list.Add(v);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取明细
        /// </summary>
        /// <returns></returns>
        public List<CompanyDetailEntity> GetCompanyDetail(string companyid, string orderId  )
        {
            List<CompanyDetailEntity> list = new List<CompanyDetailEntity>();
            const string procedure = @"select a.Id,isnull(amount,0) as amount,typeName,englishName,TypeId,date ,b.typeProviderid,b.kinds
	                                        from (select * from CompanyDetail a where CompanyId=@CompanyId and @orderId = orderId ) a
		                                        right join  dbo.DetailTypes b on b.typeid = a.detailTypesid order by b.typeProviderid,b.kinds";

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {
                Database.AddInParameter(cmd, "@CompanyId", DbType.String, companyid);
                Database.AddInParameter(cmd, "@orderId", DbType.String, orderId);
                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);
                var sqlrd = (SqlDataReader)rd.InnerReader;

                while (sqlrd.Read())
                {
                    var v = new CompanyDetailEntity()
                    {
                        Id = sqlrd["Id"].ToString(),
                        CompanyId = companyid,
                        DetailTypesId = sqlrd["TypeId"].ToString(),
                        Amount = Convert.ToDouble(sqlrd["Amount"].ToString()),
                        Date = sqlrd["date"].ToString(),
                        OrderId = orderId,

                        TypeName = sqlrd["TypeName"].ToString(),
                        EnglishName = sqlrd["EnglishName"].ToString(),

                        Kinds = Convert.ToInt32(sqlrd["kinds"].ToString()),
                        TypeProviderid = Convert.ToInt32(sqlrd["typeProviderid"].ToString())
                    };
                    list.Add(v);
                }
            }
            return list;
        }

        ///// <summary>
        ///// 获取明细
        ///// </summary>
        ///// <returns></returns>
        //public List<CompanyDetail_NewEntity> GetCompanyDetail_New(string companyid, string orderid)
        //{
        //    List<CompanyDetail_NewEntity> list = new List<CompanyDetail_NewEntity>();
        //    const string procedure = "select * from CompanyDetail where CompanyId=@CompanyId and @orderId = orderId";

        //    using (var cmd = Database.GetSqlStringCommand(procedure))
        //    {
        //        Database.AddInParameter(cmd, "@CompanyId", DbType.String, companyid);
        //        Database.AddInParameter(cmd, "@orderId", DbType.String, orderid);
        //        var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);
        //        var sqlrd = (SqlDataReader)rd.InnerReader;

        //        while (sqlrd.Read())
        //        {
        //            var v = new CompanyDetail_NewEntity()
        //            {
        //                CompanyId = companyid,
        //                OrderId = orderid,
        //                Amount = Convert.ToDouble(sqlrd["Amount"].ToString()),
        //                ChineseName = sqlrd["ChineseName"].ToString(),
        //                EnglishName = sqlrd["EnglishName"].ToString()
        //            };
        //            list.Add(v);
        //        }
        //    }
        //    return list;
        //}
        

        /// <summary>
        /// 新增明细
        /// </summary>
        public bool CreateCompanyDetail(List<CompanyDetailEntity> list, out string msg)
        {
            msg = "";

            using (DbConnection conn = Database.CreateConnection())
            {
                conn.Open();
                DbTransaction tran = conn.BeginTransaction();

                try
                {
                    foreach (var v in list)
                    {
                        if(v.Amount!=0)
                        {
                            CreateCompanyDetail(v, tran);
                        }
                    }
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    msg = ex.Message;
                    tran.Rollback();
                    return false;
                }

            }
            return true;
        }

        /// <summary>
        /// 新增明细
        /// </summary>
        public void CreateCompanyDetail(CompanyDetailEntity entity, DbTransaction transaction)
        {
            const string sql = @"
                    INSERT INTO [CompanyDetail]
                                           ([CompanyId]
                                           ,[DetailTypesId]
                                           ,[Amount]
                                           ,[Date],OrderId)   
                         VALUES(@CompanyId,@DetailTypesId,@Amount,@Date,@OrderId)";


            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyId", DbType.String, entity.CompanyId);
                Database.AddInParameter(cmd, "@DetailTypesId", DbType.String, entity.DetailTypesId);
                Database.AddInParameter(cmd, "@Amount", DbType.String, entity.Amount);
                Database.AddInParameter(cmd, "@Date", DbType.String, entity.Date);
                Database.AddInParameter(cmd, "@OrderId", DbType.String, entity.OrderId);

                Database.ExecuteNonQuery(cmd, transaction);
            }
        }

        /// <summary>
        /// 删除明细
        /// </summary>
        public void DeleteCompanyDetail(string CompanyId, string OrderId)
        {
            const string sql = @"
                             delete [CompanyDetail] where  CompanyId=@CompanyId and @orderId = orderId";


            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyId", DbType.String, CompanyId);
                Database.AddInParameter(cmd, "@OrderId", DbType.String, OrderId);

                Database.ExecuteNonQuery(cmd);
            }
        }

        public DataTable GetCompanyDetailTable(string companyId, string orderId)
        {
            const string procedure = "[Company_GetCompanyDetailTable]";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                Database.AddInParameter(cmd, "@CompanyId", DbType.String, companyId);
                Database.AddInParameter(cmd, "@orderId", DbType.String, orderId);

                DataSet ds = Database.ExecuteDataSet(cmd);

                return ds.Tables[0];
            }

        }
        /// <summary>
        /// 盈利亏损
        /// </summary>
        /// <param name="companyId"></param>
        /// <param name="date"></param>
        /// <returns></returns>
        public double GetCompanyProfitLosses(string companyId, string orderID)
        {
            const string procedure = "[Company_GetCompanyProfitLosses]";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                Database.AddInParameter(cmd, "@CompanyId", DbType.String, companyId);
                Database.AddInParameter(cmd, "@orderID", DbType.String, orderID);

                return Convert.ToDouble(Database.ExecuteScalar(cmd));
            }
        }

        /// <summary>
        /// 计算购货凭证
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <returns></returns>
        public int CalculationCompanyPurchase(string CompanyID, string OrderID)
        {
            const string sql = "Company_CalculationCompanyPurchase";

            using (var cmd = Database.GetStoredProcCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, OrderID);

                return Convert.ToInt32(Database.ExecuteScalar(cmd));
            }
        }

        /// <summary>
        /// 计算销货凭证
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <returns></returns>
        public int CalculationCompanySales(string CompanyID, string orderID)
        {
            const string sql = "Company_CalculationCompanySales";

            using (var cmd = Database.GetStoredProcCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@orderID", DbType.String, orderID);

                return Convert.ToInt32(Database.ExecuteScalar(cmd));
            }
        }


        /// <summary>
        /// 获取利得税
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <returns></returns>
        public double GetProfitTax(string CompanyID, string OrderID)
        {
            const string sql = "select isnull((select value From CompanyProfitTax where  CompanyId=@CompanyID and @OrderID = OrderID)-0,0)";

            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, OrderID);

                return Convert.ToDouble(Database.ExecuteScalar(cmd));
            }
        }

        /// <summary>
        /// 获取管理费
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <returns></returns>
        public double GetBankCharge(string CompanyID, string OrderID)
        {
            const string sql = "select isnull((select sum(hkMoney) from companyBANK where OrderID=@OrderID and explain = 3),0)";

            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, OrderID);

                return Convert.ToDouble(Database.ExecuteScalar(cmd));
            }
        }

        /// <summary>
        /// 获取利息
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <returns></returns>
        public double GetBankInterestIncome(string CompanyID, string OrderID)
        {
            const string sql = @"select Round((select sum(hkMoney) from companyBANK where OrderID=@OrderID and explain = 2),0)
			                                    -isnull((select sum(hkMoney) from companyBANK where OrderID=@OrderID and explain = 4),0)";

            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, OrderID);

                return Convert.ToDouble(Database.ExecuteScalar(cmd));
            }
        }

        /// <summary>
        /// 新增利得税
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <param name="date"></param>
        /// <param name="value"></param>
        public void InsertProfitTax(string CompanyID, string OrderID, double value)
        {
            const string sql = @"
                    delete CompanyProfitTax where CompanyId = @CompanyId and [OrderID] = @OrderID ;
                    INSERT INTO [CompanyProfitTax]
                                           ([CompanyId]
                                           ,[OrderID]
                                           ,[value])   
                         VALUES(@CompanyId,@OrderID,@value)";


            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyId", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, OrderID);
                Database.AddInParameter(cmd, "@value", DbType.String, value);

                Database.ExecuteNonQuery(cmd);
            }
        }


        /// <summary>
        /// 计算利得税
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <returns></returns>
        public double CalculationProfitTax(string CompanyID, string OrderID)
        {
            const string sql = "Company_CalculationProfitTax";

            using (var cmd = Database.GetStoredProcCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, OrderID);

                return Convert.ToDouble(Database.ExecuteScalar(cmd));
            }
        }

        /// <summary>
        /// 获取利润
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <returns></returns>
        public double GetProfit(string CompanyID, string OrderID)
        {
            const string sql = "Company_GetProfit";

            using (var cmd = Database.GetStoredProcCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, OrderID);

                return Convert.ToDouble(Database.ExecuteScalar(cmd));
            }
        }

        /// <summary>
        /// 获取公司评价
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <returns></returns>
        public string GetEvaluation(string CompanyID, string OrderID)
        {
            const string sql = "select isnull((select value From CompanyEvaluation where  CompanyId=@CompanyID and @OrderID = OrderID),'')";

            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, OrderID);

                return Database.ExecuteScalar(cmd).ToString();
            }
        }

        /// <summary>
        /// 新增公司评价
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <param name="date"></param>
        /// <param name="value"></param>
        public void InsertEvaluation(string CompanyID, string OrderID, string value)
        {
            const string sql = @"
                    delete CompanyEvaluation where CompanyId = @CompanyId and [OrderID] = @OrderID ;
                    INSERT INTO [CompanyEvaluation]
                                           ([CompanyId]
                                           ,[OrderID]
                                           ,[value])   
                         VALUES(@CompanyId,@OrderID,@value)";


            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyId", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, OrderID);
                Database.AddInParameter(cmd, "@value", DbType.String, value);

                Database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 获取收入
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public int GetRevenue(string CompanyID, string orderId)
        {
            //const string sql = "select isnull((select sum(hkmoney) from dbo.CompanyBank where explain = 0 and  CompanyId=@CompanyID and @orderId = orderId),0)";
            const string sql =
                "select isnull((select  Round(sum( Round(hkmoney,2)),0) from dbo.CompanySales where orderid ='1209130014'),0)";
            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@orderId", DbType.String, orderId);

                return (int)Convert.ToDouble(Database.ExecuteScalar(cmd).ToString());
            }
        }

        /// <summary>
        /// 获取成本
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public int GetCost(string CompanyID, string orderID)
        {
            //const string sql = "select isnull((select sum(hkmoney) from dbo.CompanyBank where explain = 1 and  CompanyId=@CompanyID and @orderID =orderID),0)";

            const string sql = "  select isnull((select Round(sum( Round(hkmoney,2)),0) from dbo.CompanyPurchase where orderid =@orderID ),0)";

            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@orderID", DbType.String, orderID);

                return (int)Convert.ToDouble(Database.ExecuteScalar(cmd).ToString());
            }
        }

        /// <summary>
        /// 删除银行对账单
        /// </summary>
        public void DeleteCompanyBank(string id)
        {
            const string sql = @"
                    delete CompanyBank where id = @id ;
                    ";


            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@id", DbType.String, id);
                Database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public void DeleteCompanyPurchase(string id)
        {
            const string sql = @"
                    delete CompanyPurchase where id = @id ;
                    ";


            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@id", DbType.String, id);
                Database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public void DeleteCompanySales(string id)
        {
            const string sql = @"
                    delete CompanySales where id = @id ;
                    ";


            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@id", DbType.String, id);
                Database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 获取上期結餘列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetCompanyBankMantissa(string CompanyID, string orderID)
        {
            const string procedure = "select * from CompanyBankMantissa where CompanyId =@CompanyId and @orderID = orderID";

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {
                Database.AddInParameter(cmd, "@CompanyId", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@orderID", DbType.String, orderID);

                DataSet ds = Database.ExecuteDataSet(cmd);

                return ds.Tables[0];
            }
        }

        /// <summary>
        /// 获取指定币种結餘
        /// </summary>
        /// <returns></returns>
        public double GetCompanyBankMantissa(string CompanyID, string orderID, string Currency)
        {
            const string procedure = "select isnull((select sum(amount) from CompanyBankMantissa where CompanyId =@CompanyId and @orderID = orderID  and (Currency=@Currency or (@Currency = 1 and Currency in (98,99)))),0) as amount";

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {
                Database.AddInParameter(cmd, "@CompanyId", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@orderID", DbType.String, orderID);
                Database.AddInParameter(cmd, "@Currency", DbType.String, Currency);

                return Convert.ToDouble(Database.ExecuteScalar(cmd));
            }
        }

        public void AddBankMantissa(string CompanyID, string orderID, string Currency, double Amount)
        {
            const string sql =
                @"
                    INSERT INTO [CompanyBankMantissa]
                               ([CompanyID]
                                ,OrderID
                               ,[Currency]
                               ,[Amount])
                         VALUES(@CompanyId,@OrderID,@Currency,@Amount)";


            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, orderID);
                Database.AddInParameter(cmd, "@Currency", DbType.String, Currency);
                Database.AddInParameter(cmd, "@Amount", DbType.String, Amount);

                Database.ExecuteNonQuery(cmd);
            }
        }

        public void DeleeteBankMantissa(string CompanyID, string orderID, string Currency)
        {
            const string sql =
                @"delete [CompanyBankMantissa]
                              where CompanyID=@CompanyId and orderID= @OrderID and Currency=@Currency";


            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, orderID);
                Database.AddInParameter(cmd, "@Currency", DbType.String, Currency);

                Database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 获取最后银行結餘
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <param name="orderID"></param>
        /// <param name="Currency"></param>
        /// <returns></returns>
        public double GetLastCompanyBankMantissa(string CompanyID, string orderID, string Currency)
        {
            const string sql = "GetLastCompanyBankMantissa";

            using (var cmd = Database.GetStoredProcCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@orderID", DbType.String, orderID);
                Database.AddInParameter(cmd, "@Currency", DbType.String, Currency);

                return Convert.ToDouble(Database.ExecuteScalar(cmd));
            }
        }


        /// <summary>
        /// 获取银行销货、购货金额
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <param name="orderID"></param>
        /// <param name="explain">0:销货  1:购货</param>
        /// <returns></returns>
        public List<ContrastAmount> GetBankSalesAmount(string CompanyID, string orderID, string explain)
        {
            List<ContrastAmount> list = new List<ContrastAmount>();
            const string procedure = @"select CAST ( CONVERT(varchar(6) , [date], 112 ) + '01' AS datetime ) as [date] ,sum(hkmoney) as amount
                                         from dbo.CompanyBank
                                         where explain = @explain
			                                        and companyId = @CompanyID and @orderID = OrderID
                                        group by CONVERT(varchar(6) , [date], 112 ) ";

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@orderID", DbType.String, orderID);
                Database.AddInParameter(cmd, "@explain", DbType.String, explain);

                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);
                var sqlrd = (SqlDataReader)rd.InnerReader;

                while (sqlrd.Read())
                {
                    var v = new ContrastAmount()
                    {
                        Date = DateTime.Parse(sqlrd["Date"].ToString()),
                        Amont = Convert.ToDouble(sqlrd["amount"].ToString())
                    };
                    list.Add(v);
                }
            }

            return list;
        }


        /// <summary>
        /// 获取销货金额
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <param name="orderID"></param>
        /// <returns></returns>
        public List<ContrastAmount> GetSalesAmount(string CompanyID, string orderID)
        {
            List<ContrastAmount> list = new List<ContrastAmount>();
            const string procedure = @"select CAST ( CONVERT(varchar(6) , [date], 112 ) + '01' AS datetime ) as [date] ,sum(hkmoney) as amount 
                                        from dbo.CompanySales
                                         where companyId = @CompanyID and @orderID = OrderID
                                        group by CONVERT(varchar(6) , [date], 112 ) ";

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@orderID", DbType.String, orderID);

                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);
                var sqlrd = (SqlDataReader)rd.InnerReader;

                while (sqlrd.Read())
                {
                    var v = new ContrastAmount()
                    {
                        Date = DateTime.Parse(sqlrd["Date"].ToString()),
                        Amont = Convert.ToDouble(sqlrd["amount"].ToString())
                    };
                    list.Add(v);
                }
            }

            return list;
        }

        /// <summary>
        /// 获取购货金额
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <param name="orderID"></param>
        /// <returns></returns>
        public List<ContrastAmount> GetPurchaseAmount(string CompanyID, string orderID)
        {
            List<ContrastAmount> list = new List<ContrastAmount>();
            const string procedure = @"select CAST ( CONVERT(varchar(6) , [date], 112 ) + '01' AS datetime ) as [date] ,sum(hkmoney) as amount 
                                        from  dbo.CompanyPurchase
                                         where companyId = @CompanyID and @orderID = OrderID
                                        group by CONVERT(varchar(6) , [date], 112 ) ";

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@orderID", DbType.String, orderID);

                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);
                var sqlrd = (SqlDataReader)rd.InnerReader;

                while (sqlrd.Read())
                {
                    var v = new ContrastAmount()
                    {
                        Date = DateTime.Parse(sqlrd["Date"].ToString()),
                        Amont = Convert.ToDouble(sqlrd["amount"].ToString())
                    };
                    list.Add(v);
                }
            }

            return list;
        }


        /// <summary>
        /// 获取银行流水报表
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <param name="orderID"></param>
        /// <param name="Currency"></param>
        /// <returns></returns>
        public List<BankTableDetailEmtity> GetBankTableDetail(string CompanyID, string orderID, string Currency)
        {
            List<BankTableDetailEmtity> list = new List<BankTableDetailEmtity>();
            const string procedure = @"[GetBankTableDetail]";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, CompanyID);
                Database.AddInParameter(cmd, "@orderID", DbType.String, orderID);
                Database.AddInParameter(cmd, "@Currency", DbType.String, Currency);

                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);
                var sqlrd = (SqlDataReader)rd.InnerReader;

                while (sqlrd.Read())
                {
                    var v = new BankTableDetailEmtity()
                    {
                        Date = DateTime.Parse(sqlrd["Date"].ToString()),
                        Amount = Convert.ToDouble(sqlrd["amount"].ToString()),
                        HkMoney = Convert.ToDouble(sqlrd["HkMoney"].ToString()),
                        InterestIncome = Convert.ToDouble(sqlrd["InterestIncome"].ToString()),
                        BankCharge = Convert.ToDouble(sqlrd["BankCharge"].ToString()),
                        CurrencyName = sqlrd["CurrencyName"].ToString(),
                        Ex = sqlrd["Ex"].ToString()
                    };
                    list.Add(v);
                }
            }

            return list;
        }

        /// <summary>
        /// 新增指派会计给核数单
        /// </summary>
        public void CreateAccountantDelegate(string orderID,int empolyeeID)
        {
            const string sql = "insert into OrderAccountantRelation (OrderID,EmpolyeeID) values (@orderID,@empolyeeID)";
            
            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@orderID", DbType.String, orderID);
                Database.AddInParameter(cmd, "@empolyeeID", DbType.Int32, empolyeeID);

                Database.ExecuteNonQuery(cmd);
            }
        }
    }
}
